package ph.com.gs3.formalistics.model.ldao;

import java.util.ArrayList;
import java.util.List;

import ph.com.gs3.formalistics.global.utilities.logging.FLLogger;
import ph.com.gs3.formalistics.model.LocalDataAccessObject;
import ph.com.gs3.formalistics.model.tables.CommentsTable;
import ph.com.gs3.formalistics.model.vo.business.Comment;
import ph.com.gs3.formalistics.model.vo.business.User;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

public class CommentsLDAO extends LocalDataAccessObject {

	public CommentsLDAO(Context context) {
		super(context);
	}

	// ===================================================================================================
	// {{ Query Methods

	public String getLatestCommentDateCreated(int userId) {

		// TODO: change this later with a different last update date
		String query = "SELECT c." + CommentsTable.COL_DATE_CREATED + " FROM Comments c "
		        + "LEFT JOIN User_Documents ud ON ud.document_id=c.document_id "
		        + "WHERE ud.user_id = ? " + "ORDER BY c.date_created DESC LIMIT 1";

		Cursor cursor = database.rawQuery(query, new String[] { Integer.toString(userId) });

		String lastUpdateDate = null;

		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			int dateCreatedIndex = cursor.getColumnIndexOrThrow(CommentsTable.COL_DATE_CREATED);
			lastUpdateDate = cursor.getString(dateCreatedIndex);
		}
		cursor.close();

		return lastUpdateDate;

	}

	public String getLatestFormDocumentsCommentDateCreated(int userId, int formId) {

		String query = "SELECT c." + CommentsTable.COL_DATE_CREATED + " FROM Comments c  "
		        + "LEFT JOIN User_Documents ud ON ud.document_id=c.document_id "
		        + "LEFT JOIN Documents d ON d._id = c.document_id "
		        + "LEFT JOIN Forms f ON f._id = d.form_id " + "WHERE ud.user_id = ? AND f._id = ? "
		        + "ORDER BY c.date_created DESC LIMIT 1";

		Cursor cursor = database.rawQuery(query,
		        new String[] { Integer.toString(userId), Integer.toString(formId) });

		String lastUpdateDate = null;

		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			int dateCreatedIndex = cursor.getColumnIndexOrThrow(CommentsTable.COL_DATE_CREATED);
			lastUpdateDate = cursor.getString(dateCreatedIndex);
		}
		cursor.close();

		return lastUpdateDate;

	}

	public List<Comment> getDocumentComments(int documentId) {

		// @formatter:off
		String whereClause 	= "c." + CommentsTable.COL_DOCUMENT_ID + "=? AND "
			        		+ "c." + CommentsTable.COL_MARKED_FOR_DELETION	+ "=?";
		String[] whereArgs 	= {Integer.toString(documentId), "0"};
		// @formatter:on

		return queryComments(whereClause, whereArgs);

	}

	public List<Comment> getAllOutgoingComments(int authorId) {

		// @formatter:off
		String whereClause 	= "c." + CommentsTable.COL_AUTHOR_ID + "=? AND "
		        			+ "c." + CommentsTable.COL_IS_OUTGOING 		+ "=?";
		String[] whereArgs = {Integer.toString(authorId), "1"};
		// @formatter:on

		return queryComments(whereClause, whereArgs);
	}

	public List<Comment> getAllCommentsMarkedForDeletion(int authorId) {

		// @formatter:off
		String whereClause 	= "c." + CommentsTable.COL_AUTHOR_ID + "=? AND "
							+ "c." + CommentsTable.COL_MARKED_FOR_DELETION + "=?";
		String[] whereArgs = {Integer.toString(authorId), "1"};
		// @formatter:on

		return queryComments(whereClause, whereArgs);

	}

	public Comment getCommentByDbId(int id) {

		String whereClause = "c._id = ?";
		String[] whereArgs = { Integer.toString(id) };

		return queryComment(whereClause, whereArgs);

	}

	public Comment getComment(String webId, int documentId) {

		// @formatter:off
		String whereClause 	= "c." + CommentsTable.COL_WEB_ID + "=? AND "
							+ "c." + CommentsTable.COL_DOCUMENT_ID + "=?";
		String[] whereArgs 	= { webId, Integer.toString(documentId) };
		// @formatter:on

		return queryComment(whereClause, whereArgs);
	}

	public Comment getCommentById(int id) {

		String whereClause = "c." + CommentsTable.COL_ID + "=?";
		String[] whereArgs = { Integer.toString(id) };

		return queryComment(whereClause, whereArgs);

	}

	public Comment queryComment(String whereClause, String[] whereArgs) {

		// @formatter:off
		String query = "SELECT c.*, "
				+ "u._id AS author_id, u.web_id AS author_web_id, "
				+ "u.email AS author_email, "
				+ "u.display_name AS author_display_name, "
				+ "u.image_url AS author_image_url "
				+ "FROM Comments c "
				+ "LEFT JOIN Users u ON c.author_id=u._id "
				+ "WHERE " + whereClause;		
		// @formatter:on

		Cursor cursor = database.rawQuery(query, whereArgs);

		cursor.moveToFirst();

		Comment comment = null;

		if (!cursor.isAfterLast()) {
			comment = cursorToComment(cursor);
		}

		cursor.close();

		return comment;
	}

	public Comment getCommentByWebId(String webId, int companyId) {

		// @formatter:off
		String query = "SELECT c.*, " + "u._id AS author_id, u.web_id AS author_web_id, "
		        + "u.email AS author_email, " 
				+ "u.display_name AS author_display_name, "
		        + "u.image_url AS author_image_url " 
				+ "FROM Comments c "
		        + "LEFT JOIN Users u ON c.author_id = u._id "
		        + "LEFT JOIN Companies cp ON u.company_id = cp._id "
		        + "WHERE c.web_id = ? AND cp._id = ?";
		// @formatter:on
		
		Cursor cursor = database.rawQuery(query,
		        new String[] { webId, Integer.toString(companyId) });

		cursor.moveToFirst();

		Comment comment = null;

		if (!cursor.isAfterLast()) {
			comment = cursorToComment(cursor);
		}

		cursor.close();

		return comment;

	}

	public List<Comment> queryComments(String whereClause, String[] whereArgs) {
		return queryComments(whereClause, whereArgs, "ASC");
	}

	public List<Comment> queryComments(String whereClause, String[] whereArgs, String order) {

		// @formatter:off
		String query = "SELECT c.*, "
				+ "u._id AS author_id, u.web_id AS author_web_id, "
				+ "u.email AS author_email, "
				+ "u.display_name AS author_display_name, "
				+ "u.image_url AS author_image_url "
				+ "FROM Comments c "
				+ "LEFT JOIN Users u ON c.author_id=u._id "
				+ "WHERE " + whereClause + " ORDER BY c.date_created " + order;		
		// @formatter:on

		Cursor cursor = database.rawQuery(query, whereArgs);
		cursor.moveToFirst();

		List<Comment> comments = new ArrayList<>();

		while (!cursor.isAfterLast()) {
			comments.add(cursorToComment(cursor));
			cursor.moveToNext();
		}

		cursor.close();

		return comments;

	}

	// }}

	// ===================================================================================================
	// {{ Insert & Update Methods

	public Comment saveComment(Comment comment) {

		ContentValues cv = createCVFromComment(comment);

		int insertId = (int) database.insertOrThrow(CommentsTable.NAME, null, cv);
		return getCommentByDbId(insertId);

	}

	public Comment updateOutgoingDocument(int id, String resultingWebId, String dateCreated)
	        throws UpdateFailedException {

		ContentValues cv = new ContentValues();
		cv.put(CommentsTable.COL_WEB_ID, resultingWebId);
		cv.put(CommentsTable.COL_DATE_CREATED, dateCreated);
		cv.put(CommentsTable.COL_IS_OUTGOING, 0);

		String whereClause = CommentsTable.COL_ID + "=?";
		String[] whereArgs = { Integer.toString(id) };

		int affectedRows = database.update(CommentsTable.NAME, cv, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new UpdateFailedException("Comment to update not found.");
		} else {
			return getCommentById(id);
		}

	}

	public Comment updateComment(String webId, int documentId, Comment comment)
	        throws UpdateFailedException {

		ContentValues cv = createCVFromComment(comment);

		// The web id and the document id cannot be updated
		cv.remove(CommentsTable.COL_WEB_ID);
		cv.remove(CommentsTable.COL_DOCUMENT_ID);

		String whereClause = CommentsTable.COL_WEB_ID + "=? AND " + CommentsTable.COL_DOCUMENT_ID
		        + "=?";
		String[] whereArgs = { webId, Integer.toString(documentId) };

		int affectedRows = database.update(CommentsTable.NAME, cv, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new UpdateFailedException("Comment to update not found.");
		} else {
			return getComment(webId, documentId);
		}

	}

	public Comment markCommentForDeletion(int id) throws UpdateFailedException {

		ContentValues cv = new ContentValues();

		cv.put(CommentsTable.COL_MARKED_FOR_DELETION, "1");

		String whereClause = CommentsTable.COL_ID + "=?";
		String[] whereArgs = { Integer.toString(id) };

		int affectedRows = database.update(CommentsTable.NAME, cv, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new UpdateFailedException("Comment to update not found.");
		} else {
			return getCommentById(id);
		}

	}

	public void deleteComment(int id) throws DeleteFailedException {

		String whereClause = CommentsTable.COL_ID + "=?";
		String[] whereArgs = { Integer.toString(id) };

		int affectedRows = database.delete(CommentsTable.NAME, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new DeleteFailedException("No comments with id " + id + " found to be deleted");
		} else if (affectedRows > 1) {
			FLLogger.w(TAG,
			        "There are more than one comments deleted when deleting comment with id " + id);
		}

	}

	public void deleteComment(String webId, int authorCompanyId) throws DeleteFailedException {

		Comment commentToDelete = getCommentByWebId(webId, authorCompanyId);
		if (commentToDelete == null) {
			throw new DeleteFailedException("Comment to delete not found in the database");
		}
		deleteComment(commentToDelete.getId());

	}

	// }}

	// ===================================================================================================
	// {{ Parser Methods

	private User cursorToUser(Cursor cursor) {

		// @formatter:off
		int idIndex 			= cursor.getColumnIndexOrThrow("author_id");
		int webIdIndex 			= cursor.getColumnIndexOrThrow("author_web_id");
		int emailIndex 			= cursor.getColumnIndexOrThrow("author_email");
		int displayNameIndex 	= cursor.getColumnIndexOrThrow("author_display_name");
		int imageURLIndex 		= cursor.getColumnIndexOrThrow("author_image_url");

		int id 				= cursor.getInt(idIndex);
		String webId 		= cursor.getString(webIdIndex);
		String email 		= cursor.getString(emailIndex);
		String displayName 	= cursor.getString(displayNameIndex);
		String imageURL 	= cursor.getString(imageURLIndex);
		// @formatter:on

		User user = new User();

		user.setId(id);
		user.setWebId(webId);

		user.setEmail(email);
		user.setDisplayName(displayName);

		user.setImageURL(imageURL);

		return user;

	}

	private Comment cursorToComment(Cursor cursor) {
		User author = cursorToUser(cursor);

		// @formatter:off
		int idIndex 				= cursor.getColumnIndexOrThrow(CommentsTable.COL_ID);
		int webIdIndex 				= cursor.getColumnIndexOrThrow(CommentsTable.COL_WEB_ID);

		int documentIdIndex			= cursor.getColumnIndexOrThrow(CommentsTable.COL_DOCUMENT_ID);

		int textIndex 				= cursor.getColumnIndexOrThrow(CommentsTable.COL_TEXT);
		int dateCreatedIndex 		= cursor.getColumnIndexOrThrow(CommentsTable.COL_DATE_CREATED);
		int isOutgoingIndex			= cursor.getColumnIndexOrThrow(CommentsTable.COL_IS_OUTGOING);
		int markedForDeletionIndex	= cursor.getColumnIndexOrThrow(CommentsTable.COL_MARKED_FOR_DELETION);

		int id 						= cursor.getInt(idIndex);
		String webId 				= cursor.getString(webIdIndex);

		int documentId				= cursor.getInt(documentIdIndex);

		String text 				= cursor.getString(textIndex);
		String dateCreated 			= cursor.getString(dateCreatedIndex);
		boolean isOutgoing			= cursor.getInt(isOutgoingIndex) == 1;
		boolean markedForDeletion	= cursor.getInt(markedForDeletionIndex) == 1;
		// @formatter:on

		Comment comment = new Comment();

		comment.setId(id);
		comment.setWebId(webId);

		comment.setDocumentId(documentId);
		comment.setText(text);
		comment.setDateCreated(dateCreated);
		comment.setOutgoing(isOutgoing);
		comment.setMarkedForDeletion(markedForDeletion);

		comment.setAuthor(author);

		return comment;

	}

	private ContentValues createCVFromComment(Comment comment) {

		ContentValues cv = new ContentValues();

		cv.put(CommentsTable.COL_WEB_ID, comment.getWebId());

		cv.put(CommentsTable.COL_DOCUMENT_ID, comment.getDocumentId());

		cv.put(CommentsTable.COL_TEXT, comment.getText());
		cv.put(CommentsTable.COL_DATE_CREATED, comment.getDateCreated());

		cv.put(CommentsTable.COL_AUTHOR_ID, comment.getAuthor().getId());
		cv.put(CommentsTable.COL_IS_OUTGOING, comment.isOutgoing() ? 1 : 0);
		cv.put(CommentsTable.COL_MARKED_FOR_DELETION, comment.isMarkedForDeletion() ? 1 : 0);

		return cv;

	}

	// }}

}
